--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_UtilityAssetXMissionStructuresPoles_SelectForExcel')
	DROP PROCEDURE up_UtilityAssetXMissionStructuresPoles_SelectForExcel
GO

CREATE PROCEDURE up_UtilityAssetXMissionStructuresPoles_SelectForExcel
	 @UtilityFk int 
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
AS

SET NOCOUNT ON	


SELECT 
 	 Pole.[Utility_Asset_XMissionStructures_PoleId] AS [Asset]
	,Maps.[Name] AS [Map]
	,SectionFk AS [Section]
	,TopType.[Name] AS [Top Type]
	,NULL AS [Kv]
	,Species.[Name] AS [Species]
	,Class.[Name] AS [Class]
	,Pole.[Length] AS [Length]
	,Pole.[Year] AS [Year]
	,Treatment.[Name] AS [Treatment]
	,Pole.[Latitude] AS [Latitude]
	,Pole.[Longitude] AS [Longitude]	
	,Pole.Number AS [Number]	
	,Pole.[LVisit] AS [Last Visit]
	,Pole.[Utility_Asset_XMissionStructureFK] AS [Structure Id]
FROM tb_Utility_Asset_XMissionStructures_Poles Pole
INNER JOIN tb_Utility_Asset_XMissionStructures Structure
	ON Structure.Utility_Asset_XMissionStructureId = Pole.Utility_Asset_XMissionStructureFK
LEFT JOIN tb_XMissionStructures_PoleTopTypes TopType
	ON TopType.XMissionStructures_PoleTopTypeId = Pole.XMissionStructures_PoleTopTypeFk
LEFT JOIN tb_XMissionStructures_PoleSpecies Species
	ON Species.XMissionStructures_PoleSpeciesId = Pole.XMissionStructures_PoleSpeciesFk
LEFT JOIN tb_XMissionStructures_PoleTreatments Treatment
	ON Treatment.XMissionStructures_PoleTreatmentId = Pole.XMissionStructures_PoleTreatmentFk
LEFT JOIN tb_XMissionStructures_PoleClasses Class
	ON Class.XMissionStructures_PoleClassId = Pole.XMissionStructures_PoleClassFk
LEFT JOIN tb_Utility_Polygons Maps
	ON Maps.Utility_PolygonId = Pole.[MapFk]
WHERE
	Structure.[UtilityFK] = @UtilityFk

GO

